Migrating a Cassandra SSTable via COPY TO and COPY FROM

This article is to show you how to migrate a Cassandra SSTable from one server to another.

For example: we will migrate a table named mytable in a keyspace named mykeyspace from one instance(Cassandra-4951)to another instance(Cassandra-5004).

What is COPY TO?

Exports data from a table to a comma-separated values (CSV) file or delimited text file. Each row is written to a line in the target file with the fields separated by the delimiter.

What is COPY FROM?

Imports data from a comma-separated values (CSV) file or a delimited text file into an existing table. Each line in the source file is imported as a row. All rows in the dataset must contain the same number of fields and have values in the PRIMARY KEY fields.

The process verifies the PRIMARY KEY and updates existing records. If HEADER = false and no column names are specified, the fields are imported in deterministic order. When HEADER = true, the first row of a file is a header row.

Note: Only use COPY FROM to import datasets that have less than two million rows. To import large datasets, use sstableloader.

1. Back up the Original SSTable

Check the original SSTable

First, log in to the Control Panel and locate your target deployment on the Home page or the My Applications page.

Click Manage > Shell / SSH tab to go to the Shell / SSH page.

After that, click the connect icon to open the shell interface.

Then, log in to Cassandra Server using Command below:

cqlsh -u cassandra -p $ROOT_PASS

cqlsh> desc mykeyspace;

cqlsh> use mykeyspace;

cqlsh> desc mytable;

cqlsh> select * from mykeyspace.mytable;

Note: "mykeyspace" is the keyspace name, "mytable" is the table name.

2. Export Data

Export SQL statement

The SQL statement is for creating keyspace and table.

Be sure to replace the <mykeyspace> and <mytable> with your keyspace name and table name respectively.

cqlsh -u cassandra -p $ROOT_PASS -e "desc keyspace <mykeyspace>" > <mykeyspace>.<mytable>

mykeyspace.mytable will contain the SQL statement that is for creating keyspace and table.

Export data via COPY TO

Use the COPY TO command like below to export your data.

cqlsh -u cassandra -p $ROOT_PASS

cqlsh> copy mykeyspace.mytable to '/cloudclusters/mykeyspace.mytable.csv';

3. Transfer files

After step 1 and step 2, you will get two files under the /cloudclusters directory. The files are "mykeyspace" and "mykeyspace.mytable.csv".

Now we need to download the files from original server and transfer them to the target using FTP tool like FileZilla client. You can refer to Transferring Data Using FTPS for help.

4. Import Data

Create a keyspace and a table in the destination server

Click the "Connect" icon to launch a shell session on the Shell/SSH page.

There should be two files under /cloudclusters if you have uploaded the files into the destination server.

Execute the command below to create your keyspace and table.

cqlsh -u cassandra -p $ROOT_PASS -f /cloudclusters/mykeyspace.mytable

Import data using COPY FROM

Execute the command below to import your data:

cqlsh -u cassandra -p $ROOT_PASS

cqlsh> COPY mykeyspace.mytable FROM '/cloudclusters/mykeyspace.mytable.csv';

cqlsh> select * from mykeyspace.mytable;

Now you've migrated your data successfully. Feel free to contact us if any help is needed.